const express = require("express");
const multer = require('multer');
const fs =require('fs');
const path = require('path');
const router = express.Router();
const pool = require("../pool.js");
const utils = require("../utils/index.js");
var upload = multer({ dest: 'uploads/' });
var xlsx = require('xlsx');
const {systemSettings} =require("../utils/menuString");
const APP_URL = 'https://api.weixin.qq.com/sns/jscode2session'
const md5 = require('md5');
const cron = require('node-cron');
const dayjs = require('dayjs');
const CHANGE_TIME = '2024-06-05 10:00:00'; 
// const CHANGE_TIME = '2024-06-03 12:00:00'; 





/**
 * @swagger
 * components:
 *  securitySchemes:
 *   ApiKeyAuth:
 *     type: apiKey 
 *     in: header 
 *     name: Authorization
 */

  
/**
 * @swagger
 * /admin/login:
 *   post:
 *     tags:
 *       - 管理后台-用户相关
 *     summary: 用户登录
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name:
 *                 type: string
 *                 description: The user's name.
 *                 example: test
 *               pwd:
 *                 type: string
 *                 description: The user's pwd.
 *                 example: 12345
 *     responses:
 *       1:
 *         description: 成功
 *       -1:
 *         description: 失败
 */
//登录
router.post("/login", async (req, res) => {
    console.log(11,req.body);
    let sql = "SELECT id,admin FROM user WHERE name=? AND pwd=?", obj = req.body;
    console.log(obj.name, obj.pwd);
    pool.query(sql, [obj.name, obj.pwd], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        if (result.length == 0) return res.send(utils.returnData({code: -1, msg: "请输入正确的用户名和密码！",err,req}));
        let uid = result[0].id, admin = result[0].admin,name = obj.name;
        let token = utils.setToken({uid});
        res.send(utils.returnData({data: {uid, name, token, admin}}));
    });
});
router.post("/loginMini", async (req, res) => {
    console.log(11,req.body);
    let obj = req.body;
    let md5pwd=md5(obj.password)
    let {total}=await utils.getSum({name:"user",where:`WHERE phone = "${obj.phone}" and pwd = "${md5pwd}"`,res,req});
    if(total>0){
        res.send(utils.returnData({data: {
            uid:0,
            phone:obj.phone,
            admin:1,
        }}));
    }else{
        let sql = "SELECT * FROM t_user WHERE phone=? AND password=?";
        pool.query(sql, [obj.phone, obj.password], (err, result) => {
            if (err) return res.send(utils.returnData({code: -1,err,req}));
            if (result.length == 0) return res.send(utils.returnData({code: -1, msg: "请输入正确的用户名和密码！",err,req}));
            res.send(utils.returnData({data: {
                uid:result[0].id,
                phone:obj.phone,
                admin:0,
            }}));
        });
    }
});
//获取用户信息
router.post("/getUserInfo",async (req,res)=>{
    let user = await utils.getUserRole(req, res);
    let sql = `SELECT b.menu_bg AS menuBg,b.menu_sub_bg AS menuSubBg,b.menu_text AS menuText,b.menu_active_text AS menuActiveText,b.menu_sub_active_text AS menuSubActiveText,b.menu_hover_bg AS menuHoverBg FROM theme AS b WHERE user_id=?`;
    pool.query(sql,[user.user.id],(err,result)=>{
        if(err||result.length===0) return  res.send(utils.returnData({code:-1,err,req}));
        res.send(utils.returnData({data:{...user,theme:result[0]}}));
    })

})


function getRouter(req,res,sidebar=false){
    return new Promise(async (resolve, reject)=>{
        let sql = "SELECT id,parent_id AS parentId,path,hidden,redirect,always_show AS alwaysShow,name,layout,parent_view AS parentView,meta,component,sort,update_time AS updateTime,alone,role_key AS roleKey,menu_type AS menuType FROM router_menu ORDER BY sort ASC, update_time DESC";
        let userRole = await utils.getUserRole(req, res);
        if (userRole == -1) return res.send(utils.returnData({code: -1,req}));
        if (!userRole.userRole || userRole.userRole == null || userRole.userRole == "null") userRole.userRole = "";
        //角色权限
        let roles = userRole.userRole.split(",");
        pool.query(sql, (err, result) => {
            if (err) return res.send(utils.returnData({code: -1,err,req}));
            let list = [...result],routerArr=[];
            let filterAsyncRoutes = (lists, parentId, pathView = "") => {
                let resArr = [], obj = {};
                lists.map((t) => {
                    let objs = {...t};
                    try {objs.meta = JSON.parse(objs.meta);} catch (err) {objs.meta = {};}
                    objs.title = objs.meta.title || "---";
                    objs.pathView = t.path;
                    //按钮自动隐藏
                    if(objs.menuType==="F") objs.hidden=1;
                    //递归
                    if (objs.parentId == parentId) {
                        objs.path = pathView + objs.path;
                        obj = {...objs, children: filterAsyncRoutes(list, objs.id, objs.path)};
                        //菜单下有子级，单独拿出来
                        if(obj.menuType==="C"&&obj.children&&obj.children.length!=0){
                            routerArr.push(...obj.children)
                            sidebar&&delete obj.children;
                        }
                        //如果是总管理
                        if (userRole.user.admin == 1 || userRole.roleAdmin ) {resArr.push(obj);} else {
                            //只拿角色权限通过的
                            if (roles.some((role) => obj.id == role)) resArr.push(obj);
                        }
                    }
                });
                return resArr;
            };
            let routerMenu = filterAsyncRoutes(list, 0, "");
            //如果是独立的（一级）
            sidebar&&routerMenu.forEach(t=>{
                if(t.menuType==="C"&&(!t.children||t.children.length===0)){
                    t.layout=1;
                    t.children=[{...t, layout:0, alone:1, children:undefined,}]
                    t.path="/"+Math.random();
                }
            });
            resolve({routerMenu,routerArr})
        });

    })
}
//获取路由 侧边栏
router.post("/getRouter", async (req, res) => {
    let {routerMenu,routerArr}=await getRouter(req,res,true);
    function bianpinghua(list){
        let arr=[];
        list.map(t=>{
            if(t.children&&t.children.length) arr.push(...bianpinghua(t.children))
            arr.push({...t,layout:1,path:"/"+Math.random(),children: [{...t,layout:0, alone:1, children:undefined}],hidden:1});
        })
        return arr
    }
    routerArr=bianpinghua(routerArr);
    routerArr= routerArr.filter((obj, index, self) => index === self.findIndex((t) => (t.id === obj.id)));
    res.send(utils.returnData({data:{routerMenu:routerMenu.concat(routerArr)}}))
});
//菜单管理获取
router.post("/getRouterSystem", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuQuery]});
    let {routerMenu}=await getRouter(req,res);
    res.send(utils.returnData({data:{routerMenu}}));
})
//添加菜单
router.post("/addMenu", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuAdd]});
    let sql = "INSERT INTO router_menu(parent_id,path,hidden,name,layout,parent_view,meta,component,sort,alone,role_key,menu_type,update_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
        obj = req.body;
    let meta = {};
    await utils.existName({sql:"SELECT id FROM router_menu WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    await utils.existName({sql:"SELECT id FROM router_menu WHERE name=?",name:obj.name,res,msg:"页面名称已存在！！",req});
    meta.title = obj.title;
    meta.icon = obj.icon;
    meta.noCache = obj.noCache;
    pool.query(sql, [obj.parentId, obj.path, obj.hidden, obj.name, obj.parentId == 0 ? 1 : 0, obj.parentView, JSON.stringify(meta), obj.component, obj.sort, obj.alone, obj.roleKey, obj.menuType,new Date(),], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//修改菜单
router.post("/changeMenu", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuUp]});
    let sql = "UPDATE  router_menu SET parent_id=?,path=?,hidden=?,name=?,layout=?,parent_view=?,meta=?,component=?,sort=?,alone=?,role_key=?,menu_type=?,update_time=? WHERE id=?",
        obj = req.body;
    let judgeUserNameRes = await utils.judgeUserName({sql:"SELECT role_key FROM router_menu WHERE  id=?",sqlName:"role_key",name:obj.roleKey,id:obj.id});
    if(judgeUserNameRes===1)await utils.existName({sql:"SELECT id FROM router_menu WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    let judgeUserNameRes2 = await utils.judgeUserName({sql:"SELECT name FROM router_menu WHERE  id=?",sqlName:"name",name:obj.name,id:obj.id});
    let meta = {};
    if(judgeUserNameRes2===1)await utils.existName({sql:"SELECT id FROM router_menu WHERE name=?",name:obj.name,res,msg:"页面名称已存在！",req});
    meta.title = obj.title;
    meta.icon = obj.icon;
    meta.noCache = obj.noCache;
    pool.query(sql, [obj.parentId, obj.path, obj.hidden, obj.name, obj.parentId == 0 ? 1 : 0, obj.parentView, JSON.stringify(meta), obj.component, obj.sort, obj.alone, obj.roleKey, obj.menuType,new Date(), obj.id,], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//删除菜单
router.post("/delMenu", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.menus.menuDelte]});
    let sql = "DELETE FROM router_menu WHERE id=?";
    let selectSql = "SELECT id FROM router_menu WHERE parent_id=?";
    let obj = req.body;
    pool.query(selectSql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        if (result.length != 0) return res.send(utils.returnData({code: -1, msg: "删除失败，请先删除子级",err,req}));
        pool.query(sql, [obj.id], (err2, result2) => {
            if (err2) return res.send(utils.returnData({code: -1,err,req}));
            res.send(utils.returnData({data: result}));
        });
    });
});
//查询角色
router.post("/getRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleQuery]});
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"roles",where:`WHERE name LIKE "%${obj.name||''}%"`,res,req});
    let sql = `SELECT id,name,roles,checked_roles AS checkedRoles,role_key AS roleKey,create_time AS createTime FROM roles WHERE name LIKE "%${obj.name||''}%" LIMIT ?,?`;
    pool.query(sql,[page,size] ,(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
//查询角色全部
router.post("/getRolesAll", async (req, res) => {
    let sql = `SELECT id,name,roles,checked_roles AS checkedRoles,role_key AS roleKey FROM roles`;
    pool.query(sql,(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//添加角色
router.post("/addRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleAdd]});
    let sql = "INSERT INTO roles(name,roles,checked_roles,role_key,create_time) VALUES (?,?,?,?,?)", obj = req.body;
    await utils.existName({sql:"SELECT id FROM roles WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    // console.log(obj);
    pool.query(sql, [obj.name, obj.roles, obj.checkedRoles,obj.roleKey,new Date()], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//修改角色
router.post("/upRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleUp]});
    let sql = "UPDATE  roles SET roles=?,name=?,checked_roles=?,role_key=? WHERE id=?", obj = req.body;
    //总管理不能操作
    await utils.upAdminRole({req,res,id:obj.id});
    let judgeUserNameRes = await utils.judgeUserName({sql:"SELECT role_key FROM roles WHERE  id=?",sqlName:"role_key",name:obj.roleKey,id:obj.id});
    if(judgeUserNameRes===1)await utils.existName({sql:"SELECT id FROM roles WHERE role_key=?",name:obj.roleKey,res,msg:"权限字符已存在！",req});
    pool.query(sql, [obj.roles, obj.name, obj.checkedRoles, obj.roleKey,obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//删除角色
router.post("/delRoles", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.role.roleDelte]});
    let sql = "DELETE FROM roles WHERE id=?", obj = req.body;
    //总管理不能操作
    await utils.upAdminRole({req,res,id:obj.id});
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

//添加用户
router.post("/addUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userAdd]});
    let sql = "INSERT INTO user(name,phone,status,roles_id,remark,pwd,more_id,create_time) VALUES (?,?,?,?,?,?,?,?)", obj = req.body;
    await utils.existName({sql: "SELECT id FROM user WHERE  name=?", name: obj.name,res,msg:"用户名已被使用！",req});
    pool.query(sql, [obj.name, obj.phone,obj.status,obj.rolesId, obj.remark, obj.pwd, obj.moreId,new Date()], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err}));
        let themeSql="INSERT INTO theme(user_id,menu_bg,menu_sub_bg,menu_text,menu_active_text,menu_sub_active_text,menu_hover_bg) VALUES (?,?,?,?,?,?,?)";
        pool.query(themeSql,[result.insertId,"#304156","#304156","#bfcad5","#409eff","#fff","#001528"],(themeErr,themeRes)=>{
            if (themeErr) return res.send(utils.returnData({code: -1,err:themeErr,req}));
            res.send(utils.returnData({data: result}));
        })

    });
});

//查询用户
router.post("/getUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userQuery]});
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"user",where:`WHERE name LIKE "%${obj.name||''}%"`,res,req});
    let sql = `SELECT a.id AS id,name,phone,status,roles_id AS rolesId,remark,admin,more_id AS moreId,a.create_time AS createTime FROM user AS a  WHERE name LIKE "%${obj.name||''}%" LIMIT ?,?`;
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});


//修改主题
router.post("/upTheme", async (req, res) => {
    let sql = "UPDATE  theme SET menu_bg=?,menu_sub_bg=?,menu_text=?,menu_active_text=?,menu_sub_active_text=?,menu_hover_bg=? WHERE user_id=?", obj = req.body;
    pool.query(sql, [obj.menuBg,obj.menuSubBg,obj.menuText,obj.menuActiveText,obj.menuSubActiveText,obj.menuHoverBg,obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});



//修改用户
router.post("/upUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userUp]});
    let sql = "UPDATE  user SET name=?,phone=?,status=?,roles_id=?,remark=?,more_id=? WHERE id=?", obj = req.body;
    //总管理不能操作
    // await utils.upAdmin({req,res,id:obj.id});
    let judgeUserNameRes = await utils.judgeUserName({sql:"SELECT name FROM user WHERE  id=?",name:obj.name,id:obj.id});
    if (judgeUserNameRes === 1) await utils.existName({sql: "SELECT id FROM user WHERE  name=?", name: obj.name,res,msg:"用户名已被使用！",req});
    pool.query(sql, [obj.name, obj.phone,obj.status,obj.rolesId, obj.remark, obj.moreId, obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

//修改用户密码
router.post("/upUserPwd", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userPwd]});
    let sql = "UPDATE  user SET pwd=? WHERE id=?", obj = req.body;
    let getUserIdRes=await utils.getUserId({id:obj.id,req,res});
    if(getUserIdRes.admin===1){
        let user=await utils.getUserInfo(req,res);
        if(user.admin!==1) return res.send(utils.returnData({code: -1,msg:"总管理密码只能总管理账号修改！",req}));
    }
    pool.query(sql, [obj.pwd,obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

//删除用户
router.post("/delUser", async (req, res) => {
    await utils.checkPermi({req,res,role:[systemSettings.user.userDelte]});
    let obj = req.body;
    //总管理不能操作
    await utils.upAdmin({req,res,id:obj.id});
    let user = await utils.getUserInfo(req, res);
    if (obj.id == user.id) return res.send(utils.returnData({code: -1, msg: "无法删除正在使用中的用户~",req}));
    let sql = "DELETE FROM user WHERE id=?";
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});







  router.post('/getUserPhone', (req, res) => {
  console.log(777,req.body);
    const js_code = req.body.code
    const ART_APP_ID = systemSettings.wxpay.APPID//AppID(小程序ID)
    const ART_APP_SECRET =systemSettings.wxpay.ART_APP_SECRET //AppSecret(小程序密钥)	
    if (js_code) {
      request(`${APP_URL}?appid=${ART_APP_ID}&secret=${ART_APP_SECRET}&js_code=${js_code}&grant_type=authorization_code`, (error, response, body) => {
        if (error) throw error
        if (response.statusCode === 200) {
          const bodyInfo = JSON.parse(body)
          var sessionKey = bodyInfo.session_key
          var pc = new WXBizDataCrypt(ART_APP_ID, sessionKey)
          var data =pc.decryptData(req.body.encryptedData, req.body.iv)
          data.openId=bodyInfo.openid;
          res.send(utils.returnData({data: data}));
        } else {
            return res.send(utils.returnData({code: -1,err,req}));
        }
      })
    }
  })





//新增
router.post("/getBannerData", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT 
    (SELECT COUNT(*) FROM t_user) AS user_count,
    (SELECT COUNT(*) FROM t_customer) AS customer_count,
    (SELECT COUNT(*) FROM t_order) AS order_count;`;
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
//查询员工
router.post("/addWorker", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO t_user (phone,password,username,address,updated_time,created_time) VALUES(?,?,?,?,now(),now());`;
    await utils.existName({sql: `SELECT id FROM t_user WHERE  username=? or phone=${obj.phone}`, name: obj.username,res,msg:"员工姓名或手机号已被使用！",req});
    pool.query(sql, [obj.phone,obj.password,obj.username,obj.address],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/upWorker", async (req, res) => {
    let obj=req.body;
    let sql = `UPDATE  t_user  SET  phone=?,password=?,username=?,address=?,updated_time=?  where id=?;`;
    await utils.existName({sql: `SELECT id FROM t_user WHERE  (username=? or phone=${obj.phone}) and id!=${obj.id}`, name: obj.username,res,msg:"员工姓名或手机号已被使用！",req});
    pool.query(sql, [obj.phone,obj.password,obj.username,obj.address,new Date(),obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getWorker", async (req, res) => {
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"t_user",where:`WHERE username LIKE "%${obj.query||''}%"  or  phone LIKE "%${obj.query||''}%"`,res,req});
    let sql = `SELECT id,phone,password,username,address,updated_time AS updatedTime,created_time AS createdTime  FROM t_user   WHERE username LIKE "%${obj.query||''}%"  or  phone LIKE "%${obj.query||''}%" ORDER BY updated_time  desc  LIMIT ?,?`;
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getWorkerById", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT id,phone,password,username,address,updated_time AS updatedTime,created_time AS createdTime  FROM t_user   WHERE id=?`;
    pool.query(sql, [obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/delWorker", async (req, res) => {
    let obj = req.body;
    let sql = "DELETE FROM t_user WHERE id=?";
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//查询单位
router.post("/addUnit", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO unit (name,updated_time,created_time) VALUES(?,now(),now());`;
    await utils.existName({sql: "SELECT id FROM unit WHERE  name=?", name: obj.name,res,msg:"单位名称已被使用！",req});
    pool.query(sql, [obj.name],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/upUnit", async (req, res) => {
    let obj=req.body;
    let sql = `UPDATE  unit  SET  name=?,updated_time=?  where id=?;`;
    await utils.existName({sql: `SELECT id FROM unit WHERE  name=? and id!=${obj.id}`, name: obj.name,res,msg:"单位名称已被使用！",req});
    pool.query(sql, [obj.name,new Date(),obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getUnit", async (req, res) => {
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"unit",where:`WHERE name LIKE "%${obj.query||''}%"`,res,req});
    let sql = `SELECT id,name,updated_time AS updatedTime,created_time AS createdTime    FROM unit   WHERE name LIKE "%${obj.query||''}%"   ORDER BY updated_time desc LIMIT ?,?`;
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getUnitById", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT id,name,updated_time AS updatedTime,created_time AS createdTime    FROM unit   WHERE id =?`;
    pool.query(sql, [obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/delUnit", async (req, res) => {
    let obj = req.body;
    let sql = "DELETE FROM unit WHERE id=?";
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//查询客户
router.post("/addCustomer", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO t_customer (phone,username,address,updated_time,created_time) VALUES(?,?,?,now(),now());`;
    await utils.existName({sql: "SELECT id FROM t_customer WHERE  username=?", name: obj.username,res,msg:"客户名称已被使用！",req});
    pool.query(sql, [obj.phone,obj.username,obj.address],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/upCustomer", async (req, res) => {
    let obj=req.body;
    let sql = `UPDATE  t_customer  SET  phone=?,username=?,address=?,updated_time=?  where id=?;`;
    await utils.existName({sql: `SELECT id FROM t_customer WHERE  username=? and id!=${obj.id}`, name: obj.username,res,msg:"客户名称已被使用！",req});
    pool.query(sql, [obj.phone,obj.username,obj.address,new Date(),obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getCustomer", async (req, res) => {
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"t_customer",where:`WHERE username LIKE "%${obj.query||''}%"  or  phone LIKE "%${obj.query||''}%"`,res,req});
    let sql = `SELECT id,phone,username,address,updated_time AS updatedTime,created_time AS createdTime  FROM t_customer   WHERE username LIKE "%${obj.query||''}%"  or  phone LIKE "%${obj.query||''}%" ORDER BY updated_time  desc LIMIT ?,?`;
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getCustomerById", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT id,phone,username,address,updated_time AS updatedTime,created_time AS createdTime  FROM t_customer   WHERE id=?`;
    pool.query(sql, [obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/delCustomer", async (req, res) => {
    let obj = req.body;
    let sql = "DELETE FROM t_customer WHERE id=?";
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});
//查询工序
router.post("/addProcesses", async (req, res) => {
    let obj=req.body;
    let sql = `INSERT INTO processes (name,updated_time,created_time) VALUES(?,now(),now());`;
    await utils.existName({sql: "SELECT id FROM processes WHERE  name=?", name: obj.name,res,msg:"工序名称已被使用！",req});
    pool.query(sql, [obj.name],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/upProcesses", async (req, res) => {
    let obj=req.body;
    let sql = `UPDATE  processes  SET  name=?,updated_time=?  where id=?;`;
    await utils.existName({sql: `SELECT id FROM processes WHERE  name=? and id!=${obj.id}`, name: obj.name,res,msg:"工序名称已被使用！",req});
    pool.query(sql, [obj.name,new Date(),obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getProcesses", async (req, res) => {
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let {total}=await utils.getSum({name:"processes",where:`WHERE name LIKE "%${obj.query||''}%" `,res,req});
    let sql = `SELECT id,name,updated_time AS updatedTime,created_time AS createdTime  FROM processes   WHERE name LIKE "%${obj.query||''}%"  ORDER BY updated_time desc  LIMIT ?,?`;
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getProcessesById", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT id,name,updated_time AS updatedTime,created_time AS createdTime    FROM processes   WHERE id =?`;
    pool.query(sql, [obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/delProcesses", async (req, res) => {
    let obj = req.body;
    let sql = "DELETE FROM processes WHERE id=?";
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});


//收益
router.post("/getFinanceNoLimit", async (req, res) => {
    let obj=req.body;
    let where=''
    if(obj.stateQuery){
        where+=`and o.state = ${obj.stateQuery} `
    }
    if(obj.startDate){
        where+=`and o.create_time >= ${obj.startDate}   and o.create_time < ${obj.endDate}`
    }
    if(obj.query){
        where+=`and o.order_name LIKE CONCAT('%', ${obj.query}, '%') `
    }
    if(obj.customerId){
        where+=`and o.customer_id = ${obj.customerId} `
    }
    if(obj.workerId){
        where+=`and ow.worker_id = ${obj.workerId} `
    }
    if(obj.processId){
        where+=`and op.process_id = ${obj.processId} `
    }
    let sql = `
    SELECT 
  o.order_id AS orderId,
  o.order_name AS orderName,
  o.customer_name AS customerName,
  o.state AS orderState,
  o.create_time AS createTime,
  IF(o.state!=1,o.update_time,'') AS finishTime,
  
  op.process_id AS processId,
  op.process_name AS processName,
  op.unit_name AS unitName,
  op.customerprice,
  ROUND(op.customerprice * ow.real_number, 2) AS customerOutcome,
  ow.worker_id AS workerId,
  ow.woker_name AS workerName,
  ow.workerprice,
  ow.number,
  ow.real_number,
  ROUND(ow.workerprice * ow.real_number, 2) AS actualIncome,
  ROUND(
    IF(ow.number > 0, 
       (ow.real_number / ow.number) * 100, 
       0
    ), 2
  ) AS completionRate
FROM order_worker ow
JOIN order_process op ON ow.order_process_id = op.order_process_id
JOIN t_order o ON op.order_id = o.order_id
WHERE 1=1 
${where}
ORDER BY ow.update_time DESC`;
console.log(999,sql);
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getFinance", async (req, res) => {
    let obj=req.body;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    let where=''
    if(obj.stateQuery){
        where+=`and o.state = ${obj.stateQuery} `
    }
    if(obj.startDate){
        where+=`and o.create_time >= '${obj.startDate}'   and o.create_time < '${obj.endDate}'`
    }
    if(obj.query){
        where+=`and o.order_name LIKE CONCAT('%', ${obj.query}, '%') `
    }
    if(obj.customerId){
        where+=`and o.customer_id = ${obj.customerId} `
    }
    if(obj.workerId){
        where+=`and ow.worker_id = ${obj.workerId} `
    }
    if(obj.processId){
        where+=`and op.process_id = ${obj.processId} `
    }
    let {total}=await utils.getSumBySql({sql:`SELECT 
  count(1)
FROM order_worker ow
JOIN order_process op ON ow.order_process_id = op.order_process_id
JOIN t_order o ON op.order_id = o.order_id
WHERE 1=1 
${where}`,res,req});

    let sql = `
    SELECT 
  o.order_id AS orderId,
  o.order_name AS orderName,
  o.customer_name AS customerName,
  o.state AS orderState,
  o.create_time AS createTime,
  IF(o.state!=1,o.update_time,'') AS finishTime,
  
  op.process_id AS processId,
  op.process_name AS processName,
  op.unit_name AS unitName,
  op.customerprice,
  ROUND(op.customerprice * ow.real_number, 2) AS customerOutcome,
  ow.worker_id AS workerId,
  ow.woker_name AS workerName,
  ow.workerprice,
  ow.number,
  ow.real_number,
  ROUND(ow.workerprice * ow.real_number, 2) AS actualIncome,
  ROUND(
    IF(ow.number > 0, 
       (ow.real_number / ow.number) * 100, 
       0
    ), 2
  ) AS completionRate
FROM order_worker ow
JOIN order_process op ON ow.order_process_id = op.order_process_id
JOIN t_order o ON op.order_id = o.order_id
WHERE 1=1 
${where}
ORDER BY ow.update_time DESC  LIMIT ?,?`;
console.log(999,sql);
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
//订单信息

router.post("/addOrder", async (req, res) => {
    let obj=req.body;
    const orderId=obj.orderId;
    const orderName=obj.orderName;
    const customerId=obj.customerId;
    const customerName=obj.customerName;
    const remark=obj.remark;
    let sql = `INSERT INTO t_order (order_id,order_name,customer_id,customer_name,state,remark,update_time,create_time) VALUES(?,?,?,?,?,?,now(),now());`;
    await utils.existName({sql: "SELECT id FROM t_order WHERE  order_name=?", name: obj.orderName,res,msg:"订单名称已被使用！",req});
    pool.query(sql, [orderId, orderName, customerId, customerName, 1, remark], (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        res.send(utils.returnData({ data: result }));
    });
});
router.post("/addOrderForWorker", async (req, res) => {
        let obj=req.body;
        let sql = `INSERT INTO order_worker (order_process_id,worker_id,woker_name,phone,workerprice,number,real_number,update_time,create_time) VALUES(?,?,?,?,?,?,?,now(),now())`;
        pool.query(sql, [obj.orderProcessId,obj.workerId,obj.workerName,obj.phone, obj.workerprice, obj.number, obj.realNumber?obj.realNumber:0],(err, result) => {
            if (err) return res.send(utils.returnData({code: -1,err,req}));
            res.send(utils.returnData({data: result.insertId}));
        });
        
});
router.post("/delOrderForWorker", async (req, res) => {
    let obj = req.body;
    let sql = "DELETE FROM order_worker WHERE order_process_id=?";
    pool.query(sql, [obj.orderProcessId], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});

// 删除指定工序的指定工人记录
router.post("/delOrderForWorkerById", async (req, res) => {
    let obj = req.body;
    let sql = "DELETE FROM order_worker WHERE order_process_id=? AND worker_id=?";
    pool.query(sql, [obj.orderProcessId, obj.workerId], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});

// 更新工人记录，保留realNumber
router.post("/upOrderForWorker", async (req, res) => {
    let obj = req.body;
    let sql = `UPDATE order_worker SET 
        worker_id=?, 
        woker_name=?, 
        phone=?, 
        workerprice=?, 
        number=?, 
        real_number=?,
        update_time=now() 
        WHERE id=?`;
    pool.query(sql, [
        obj.workerId,
        obj.workerName,
        obj.phone,
        obj.workerprice,
        obj.number,
        obj.realNumber || 0,
        obj.id
    ], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        res.send(utils.returnData({data: result}));
    });
});

router.post("/insertWorkerNumber", async (req, res) => {
        let obj=req.body;
        
        // 使用事务和FOR UPDATE锁来防止竞态条件
        pool.getConnection((err, connection) => {
            if (err) return res.send(utils.returnData({code: -1, err, req}));
            
            // 关闭自动提交，确保事务有效
            connection.query('SET SESSION autocommit = 0', (err) => {
                if (err) {
                    connection.release();
                    return res.send(utils.returnData({code: -1, err, req}));
                }
                
                // 开始事务
                connection.beginTransaction((err) => {
                    if (err) {
                        connection.query('SET SESSION autocommit = 1');
                        connection.release();
                        return res.send(utils.returnData({code: -1, err, req}));
                    }
                
                    // 1. 先检查工序的计划总量和当前已录入总量
                    // 注意：不能使用 LEFT JOIN，会导致重复计算！
                    // 例如：order_worker有3条，order_worker_numberlist有21条
                    // LEFT JOIN会产生3*21=63行，SUM(ow.number)会被重复计算21次
                    // 所以应该使用子查询分别计算
                    // realTotal 必须使用 order_worker.real_number（累计总量），而不是 order_worker_numberlist.number
                    let checkSql = `
                        SELECT 
                            (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
                             FROM order_worker 
                             WHERE order_process_id = ?) AS planTotal,
                            (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
                             FROM order_worker 
                             WHERE order_process_id = ?) AS currentTotal
                        FROM (SELECT 1) AS tmp
                    `;
                
                    connection.query(checkSql, [obj.processId, obj.processId], (err, checkResult) => {
                        if (err) {
                            return connection.rollback(() => {
                                connection.query('SET SESSION autocommit = 1');
                                connection.release();
                                res.send(utils.returnData({code: -1, err, req}));
                            });
                        }
                        
                        if (checkResult.length === 0) {
                            return connection.rollback(() => {
                                connection.query('SET SESSION autocommit = 1');
                                connection.release();
                                res.send(utils.returnData({code: -1, msg: "工序不存在", req}));
                            });
                        }
                    
                        const planTotal = checkResult[0].planTotal || 0;
                        const currentTotal = checkResult[0].currentTotal || 0;
                        const newNumber = parseInt(obj.number) || 0;
                        const remaining = planTotal - currentTotal;
                        
                        console.log('工人录入数量检查(事务内):', {
                            processId: obj.processId,
                            workerId: obj.workerId,
                            planTotal: planTotal,
                            currentTotal: currentTotal,
                            newNumber: newNumber,
                            remaining: remaining
                        });
                        
                        // 2. 检查录入后是否会超过计划总量
                        if (currentTotal + newNumber > planTotal) {
                            console.log('❌ 超过限制，回滚事务');
                            return connection.rollback(() => {
                                connection.query('SET SESSION autocommit = 1');
                                connection.release();
                                res.send(utils.returnData({
                                    code: -1, 
                                    msg: `超过工序计划总量！该工序所有工人计划总量：${planTotal}，当前已录入：${currentTotal}，剩余可录入：${remaining}，本次录入：${newNumber}。请调整录入数量！`,
                                    req
                                }));
                            });
                        }
                        
                        // 3. 检查当前工序是否已完成（而不是检查订单状态）
                        // 如果当前工序的所有工人已录入总量 >= 计划总量，则工序已完成
                        if (currentTotal >= planTotal && planTotal > 0) {
                            console.log('❌ 该工序已完成，无法继续录入');
                            return connection.rollback(() => {
                                connection.query('SET SESSION autocommit = 1');
                                connection.release();
                                res.send(utils.returnData({
                                    code: -1, 
                                    msg: `该工序已完成，无法继续录入！该工序计划总量：${planTotal}，已录入：${currentTotal}`,
                                    req
                                }));
                            });
                        }
                        
                        // 4. 录入工人数量
                        console.log('✅ 通过检查，插入记录');
                        let insertSql = `INSERT INTO order_worker_numberlist (orderId,processId,workerId,number,create_time) VALUES(?,?,?,?,now())`;
                        connection.query(insertSql, [obj.orderId,obj.processId,obj.workerId,obj.number], (err, result) => {
                            if (err) {
                                console.log('❌ 插入失败，回滚');
                                return connection.rollback(() => {
                                    connection.query('SET SESSION autocommit = 1');
                                    connection.release();
                                    res.send(utils.returnData({code: -1,err,req}));
                                });
                            }
                            
                            // 5. 提交事务
                            connection.commit((err) => {
                                if (err) {
                                    console.log('❌ 提交失败，回滚');
                                    return connection.rollback(() => {
                                        connection.query('SET SESSION autocommit = 1');
                                        connection.release();
                                        res.send(utils.returnData({code: -1, err, req}));
                                    });
                                }
                                
                                console.log('✅ 事务提交成功');
                                connection.query('SET SESSION autocommit = 1');
                                connection.release();
                                
                                // 6. 检查录入后是否达到计划总量
                                const newCurrentTotal = currentTotal + newNumber;
                                let isCompleted = false;
                                if (newCurrentTotal >= planTotal) {
                                    isCompleted = true;
                                    console.log('当前工序已完成，检查订单所有工序是否完成:', obj.orderId);
                                    
                                    // 检查该订单的所有工序是否都完成
                                    let checkAllProcessSql = `
                                        SELECT 
                                            op.order_process_id,
                                            op.process_name,
                                            (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
                                             FROM order_worker 
                                             WHERE order_process_id = op.order_process_id) AS planTotal,
                                            (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
                                             FROM order_worker 
                                             WHERE order_process_id = op.order_process_id) AS realTotal
                                        FROM order_process op
                                        WHERE op.order_id = ?
                                    `;
                                    
                                    pool.query(checkAllProcessSql, [obj.orderId], (err, processResults) => {
                                        if (err) {
                                            console.error('检查所有工序状态失败:', err);
                                            return;
                                        }
                                        
                                        // 检查是否所有工序都完成
                                        let allProcessCompleted = processResults.every(process => {
                                            const pTotal = parseFloat(process.planTotal) || 0;
                                            const rTotal = parseFloat(process.realTotal) || 0;
                                            // 如果当前是本次更新的工序，使用新的总量
                                            if (process.order_process_id === obj.processId) {
                                                return newCurrentTotal >= pTotal && pTotal > 0;
                                            }
                                            return rTotal >= pTotal && pTotal > 0;
                                        });
                                        
                                        console.log('订单工序完成情况:', {
                                            orderId: obj.orderId,
                                            totalProcess: processResults.length,
                                            allCompleted: allProcessCompleted,
                                            details: processResults.map(p => ({
                                                processId: p.order_process_id,
                                                processName: p.process_name,
                                                plan: p.planTotal,
                                                real: p.realTotal,
                                                completed: p.realTotal >= p.planTotal
                                            }))
                                        });
                                        
                                        // 只有所有工序都完成时，才更新订单状态为已完成
                                        if (allProcessCompleted && processResults.length > 0) {
                                            console.log('所有工序已完成，更新订单状态为已完成:', obj.orderId);
                                            let updateOrderSql = `UPDATE t_order SET state = 2, update_time = now() WHERE order_id = ?`;
                                            pool.query(updateOrderSql, [obj.orderId], (err, updateResult) => {
                                                if (err) {
                                                    console.error('更新订单状态失败:', err);
                                                } else {
                                                    console.log('订单状态已更新为已完成');
                                                }
                                            });
                                        } else {
                                            console.log('还有其他工序未完成，订单保持进行中状态');
                                        }
                                    });
                                }
                                
                                // 7. 返回该工人的总录入量
                                let totalSql = `SELECT SUM(number) AS totalNumber FROM order_worker_numberlist WHERE orderId = ? AND processId = ? AND workerId = ?`;
                                pool.query(totalSql, [obj.orderId,obj.processId,obj.workerId], (err, totalResult) => {
                                    if (err) return res.send(utils.returnData({code: -1,err:err,req}));
                                    res.send(utils.returnData({
                                        data: totalResult[0],
                                        isCompleted: isCompleted,
                                        processCompleteInfo: {
                                            planTotal: planTotal,
                                            currentTotal: newCurrentTotal,
                                            remaining: planTotal - newCurrentTotal
                                        }
                                    }));
                                });
                            });
                        });
                    });
                });
            });
        });
});
router.post("/selctWorkerList", async (req, res) => {
        let obj=req.body;
        let sql = `SELECT id,number,create_time AS createdTime    FROM order_worker_numberlist   WHERE orderId =? and processId =? and workerId=? ORDER BY create_time DESC`;
        pool.query(sql, [obj.orderId,obj.processId,obj.workerId],(err, result) => {
            if (err) return res.send(utils.returnData({code: -1,err:err,req}));
            res.send(utils.returnData({data: result}));
        });
        
});
router.post("/deleteWorkerList", async (req, res) => {
    let obj = req.body;
    let sql = "DELETE FROM order_worker_numberlist WHERE id=?";
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err:err,req}));
        let sql2 = "UPDATE order_worker set real_number=real_number-?  WHERE id=?";
        pool.query(sql2, [obj.number,obj.orderWorkerId], (err2, result2) => {
            if (err2) return res.send(utils.returnData({code: -1,err:err2,req}));
            res.send(utils.returnData({data: result2}));
        });
    });
});
router.post("/updateWorkerNumber", async (req, res) => {
        let obj=req.body;
        
        // 1. 先获取该工人所在的工序ID
        let getProcessSql = `SELECT order_process_id FROM order_worker WHERE id = ?`;
        pool.query(getProcessSql, [obj.id], (err, processResult) => {
            if (err) return res.send(utils.returnData({code: -1, err, req}));
            
            if (processResult.length === 0) {
                return res.send(utils.returnData({code: -1, msg: "工人记录不存在", req}));
            }
            
            const orderProcessId = processResult[0].order_process_id;
            
            // 2. 检查工序的计划总量和当前其他工人的已录入总量
            let checkSql = `
            SELECT
                    SUM(ow.number) AS planTotal,
                    COALESCE(SUM(ow.real_number), 0) AS currentTotal,
                    ow2.real_number AS currentWorkerTotal
                FROM order_worker ow
                LEFT JOIN order_worker ow2 ON ow2.id = ?
                WHERE ow.order_process_id = ?
                GROUP BY ow.order_process_id, ow2.real_number
            `;
            
            pool.query(checkSql, [obj.id, orderProcessId], (err, checkResult) => {
                if (err) return res.send(utils.returnData({code: -1, err, req}));
                
                if (checkResult.length === 0) {
                    return res.send(utils.returnData({code: -1, msg: "工序不存在", req}));
                }
                
                const planTotal = checkResult[0].planTotal || 0;
                const currentTotal = checkResult[0].currentTotal || 0;
                const currentWorkerTotal = checkResult[0].currentWorkerTotal || 0;
                const newRealNumber = parseInt(obj.realNumber) || 0;
                
                // 计算其他工人的总量（当前总量 - 该工人的当前量）
                const otherWorkersTotal = currentTotal - currentWorkerTotal;
                // 计算新的总量
                const newTotal = otherWorkersTotal + newRealNumber;
                
                console.log('更新工人数量检查:', {
                    workerId: obj.id,
                    orderProcessId: orderProcessId,
                    planTotal: planTotal,
                    currentTotal: currentTotal,
                    currentWorkerTotal: currentWorkerTotal,
                    newRealNumber: newRealNumber,
                    otherWorkersTotal: otherWorkersTotal,
                    newTotal: newTotal
                });
                
                // 3. 检查更新后是否会超过计划总量
                if (newTotal > planTotal) {
                    return res.send(utils.returnData({
                        code: -1, 
                        msg: `超过工序计划总量！该工序计划总量：${planTotal}，其他工人已录入：${otherWorkersTotal}，您当前已录入：${currentWorkerTotal}，本次更新为：${newRealNumber}，将导致总量超出！剩余可录入：${planTotal - otherWorkersTotal}`,
                        req
                    }));
                }
                
                // 4. 更新工人数量
                let sql2 = `UPDATE order_worker SET real_number=?, update_time=now() WHERE id=?`;
                pool.query(sql2, [obj.realNumber, obj.id], (err, result2) => {
                    if (err) return res.send(utils.returnData({code: -1, err, req}));
                    
                    // 5. 检查更新后是否达到计划总量
                    if (newTotal >= planTotal && planTotal > 0) {
                        console.log('当前工序已完成，检查订单所有工序是否完成');
                        
                        // 检查该订单的所有工序是否都完成
                        let checkAllProcessSql = `
                            SELECT 
                                op.order_process_id,
                                op.process_name,
                                (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
                                 FROM order_worker 
                                 WHERE order_process_id = op.order_process_id) AS planTotal,
                                (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
                                 FROM order_worker 
                                 WHERE order_process_id = op.order_process_id) AS realTotal
                            FROM order_process op
                            WHERE op.order_id = (
                                SELECT order_id FROM order_process WHERE order_process_id = ?
                            )
                        `;
                        
                        pool.query(checkAllProcessSql, [orderProcessId], (err, processResults) => {
                            if (err) {
                                console.error('检查所有工序状态失败:', err);
                                return;
                            }
                            
                            // 检查是否所有工序都完成
                            let allProcessCompleted = processResults.every(process => {
                                const pTotal = parseFloat(process.planTotal) || 0;
                                const rTotal = parseFloat(process.realTotal) || 0;
                                // 如果当前是本次更新的工序，使用新的总量
                                if (process.order_process_id === orderProcessId) {
                                    return newTotal >= pTotal && pTotal > 0;
                                }
                                return rTotal >= pTotal && pTotal > 0;
                            });
                            
                            console.log('订单工序完成情况:', {
                                orderProcessId: orderProcessId,
                                totalProcess: processResults.length,
                                allCompleted: allProcessCompleted
                            });
                            
                            // 只有所有工序都完成时，才更新订单状态为已完成
                            if (allProcessCompleted && processResults.length > 0) {
                                // 获取订单ID
                                let getOrderIdSql = `SELECT order_id FROM order_process WHERE order_process_id = ?`;
                                pool.query(getOrderIdSql, [orderProcessId], (err, orderResult) => {
                                    if (err || orderResult.length === 0) {
                                        console.error('获取订单ID失败:', err);
                                        return;
                                    }
                                    
                                    const orderId = orderResult[0].order_id;
                                    console.log('所有工序已完成，更新订单状态为已完成:', orderId);
                        let updateOrderSql = `UPDATE t_order SET state = 2, update_time = now() WHERE order_id = ?`;
                                    pool.query(updateOrderSql, [orderId], (err, updateResult) => {
                            if (err) {
                                console.error('更新订单状态失败:', err);
                            } else {
                                console.log('订单状态已更新为已完成');
                                        }
                                    });
                                });
                            } else {
                                console.log('还有其他工序未完成，订单保持进行中状态');
                            }
                        });
                    }
                    
                    res.send(utils.returnData({
                        data: result2,
                        isCompleted: newTotal >= planTotal,
                        processCompleteInfo: {
                            planTotal: planTotal,
                            currentTotal: newTotal,
                            remaining: planTotal - newTotal
                        }
                    }));
                });
            });
        });
});
router.post("/addOrderForProcess", async (req, res) => {
        let obj=req.body;
            let sql2 = `INSERT INTO order_process (order_process_id,order_id,process_id,process_name,unit_id,unit_name,customerprice,update_time,create_time) VALUES(?,?,?,?,?,?,?,now(),now());`;
            pool.query(sql2, [obj.orderProcessId,obj.orderId,obj.processId, obj.processName, obj.unitId, obj.unitName, obj.customerprice],(err, result) => {
                if (err) return res.send(utils.returnData({code: -1,err,req}));
                res.send(utils.returnData({data: result.insertId}));
            });
});

router.post("/delOrderForProcess", async (req, res) => {
    let obj = req.body;
    let sql, params;
    
    if (obj.orderProcessId) {
        // 按工序ID删除特定工序
        sql = "DELETE FROM order_process WHERE order_process_id=?";
        params = [obj.orderProcessId];
    } else {
        // 按订单ID删除所有工序
        sql = "DELETE FROM order_process WHERE order_id=?";
        params = [obj.orderId];
    }
    
    pool.query(sql, params, (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/upOrderForProcess", async (req, res) => {
    let obj=req.body;
    let sql = `UPDATE order_process SET process_id=?,process_name=?,unit_id=?,unit_name=?,customerprice=?,update_time=? WHERE order_process_id=?`;
    pool.query(sql, [obj.processId, obj.processName, obj.unitId, obj.unitName, obj.customerprice, new Date(), obj.orderProcessId],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        
        // 检查是否需要回滚订单状态（当计划量增加，导致未完成时）
        // 注意：必须使用 order_worker.real_number（累计总量），避免 JOIN 重复计算
        let checkSql = `
            SELECT 
                op.order_id AS orderId,
                (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
                 FROM order_worker 
                 WHERE order_process_id = ?) AS planTotal,
                (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
                 FROM order_worker 
                 WHERE order_process_id = ?) AS currentTotal,
                o.state AS orderState
            FROM order_process op
            LEFT JOIN t_order o ON op.order_id = o.order_id
            WHERE op.order_process_id = ?
        `;
        
        pool.query(checkSql, [obj.orderProcessId, obj.orderProcessId, obj.orderProcessId], (err, checkResult) => {
            if (err) {
                console.error('检查订单状态失败:', err);
                return res.send(utils.returnData({data: result}));
            }
            
            if (checkResult.length > 0) {
                const orderId = checkResult[0].orderId;
                const planTotal = checkResult[0].planTotal || 0;
                const currentTotal = checkResult[0].currentTotal || 0;
                const orderState = checkResult[0].orderState;
                
                console.log('工序更新后检查:', {
                    orderId: orderId,
                    orderProcessId: obj.orderProcessId,
                    planTotal: planTotal,
                    currentTotal: currentTotal,
                    orderState: orderState
                });
                
                // 如果订单已完成，但当前录入量小于新的计划量，则回滚为进行中
                if (orderState === 2 && currentTotal < planTotal) {
                    console.log('订单状态回滚为进行中:', orderId);
                    let updateOrderSql = `UPDATE t_order SET state = 1, update_time = now() WHERE order_id = ?`;
                    pool.query(updateOrderSql, [orderId], (err, updateResult) => {
                        if (err) {
                            console.error('回滚订单状态失败:', err);
                        } else {
                            console.log('订单状态已回滚为进行中');
                        }
                    });
                }
                // 如果订单进行中，但当前录入量已达到新的计划量，检查所有工序是否完成
                else if (orderState === 1 && currentTotal >= planTotal && planTotal > 0) {
                    console.log('当前工序已完成，检查订单所有工序是否完成');
                    
                    // 检查该订单的所有工序是否都完成
                    let checkAllProcessSql = `
                        SELECT 
                            op.order_process_id,
                            op.process_name,
                            (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
                             FROM order_worker 
                             WHERE order_process_id = op.order_process_id) AS planTotal,
                            (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
                             FROM order_worker 
                             WHERE order_process_id = op.order_process_id) AS realTotal
                        FROM order_process op
                        WHERE op.order_id = ?
                    `;
                    
                    pool.query(checkAllProcessSql, [orderId], (err, processResults) => {
                        if (err) {
                            console.error('检查所有工序状态失败:', err);
                            return;
                        }
                        
                        // 检查是否所有工序都完成
                        let allProcessCompleted = processResults.every(process => {
                            const pTotal = parseFloat(process.planTotal) || 0;
                            const rTotal = parseFloat(process.realTotal) || 0;
                            return rTotal >= pTotal && pTotal > 0;
                        });
                        
                        console.log('订单工序完成情况:', {
                            orderId: orderId,
                            totalProcess: processResults.length,
                            allCompleted: allProcessCompleted
                        });
                        
                        // 只有所有工序都完成时，才更新订单状态为已完成
                        if (allProcessCompleted && processResults.length > 0) {
                            console.log('所有工序已完成，更新订单状态为已完成:', orderId);
                    let updateOrderSql = `UPDATE t_order SET state = 2, update_time = now() WHERE order_id = ?`;
                    pool.query(updateOrderSql, [orderId], (err, updateResult) => {
                        if (err) {
                            console.error('更新订单状态失败:', err);
                        } else {
                            console.log('订单状态已更新为已完成');
                        }
                    });
                        } else {
                            console.log('还有其他工序未完成，订单保持进行中状态');
                        }
                    });
                }
            }
            
            res.send(utils.returnData({data: result}));
        });
    });
});
router.post("/upOrder", async (req, res) => {
    let obj=req.body;
    console.log(12345,obj.state);
    let sql = `UPDATE  t_order  SET  order_name=?,customer_id=?,customer_name=?,state=?,remark=?,update_time=?  where id=?;`;
    await utils.existName({sql: `SELECT id FROM t_order WHERE  order_name=? and id!=${obj.id}`, name: obj.orderName,res,msg:"订单名称已被使用！",req});
    pool.query(sql, [obj.orderName, obj.customerId, obj.customerName, obj.state, obj.remark,new Date(),obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/updateOrderStateByMini", async (req, res) => {
    let obj=req.body;
    let sql = `UPDATE  t_order  SET  state=?,update_time=?  where id=?;`;
    pool.query(sql, [obj.state, new Date(),obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

router.post("/updateWorkerNum", async (req, res) => {
    let obj=req.body;
    let sql = `UPDATE  order_worker  SET  number=?,update_time=?  where id=?;`;
    pool.query(sql, [obj.number, new Date(),obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        
        // 检查是否需要更新订单状态（当计划量变化，导致完成状态改变时）
        // 1. 先获取该工人所在的工序ID和订单ID
        let getOrderSql = `
            SELECT 
                ow.order_process_id AS orderProcessId,
                op.order_id AS orderId
            FROM order_worker ow
            LEFT JOIN order_process op ON ow.order_process_id = op.order_process_id
            WHERE ow.id = ?
        `;
        
        pool.query(getOrderSql, [obj.id], (err, orderResult) => {
            if (err) {
                console.error('获取订单信息失败:', err);
                return res.send(utils.returnData({data: result}));
            }
            
            if (orderResult.length === 0) {
                return res.send(utils.returnData({data: result}));
            }
            
            const orderProcessId = orderResult[0].orderProcessId;
            const orderId = orderResult[0].orderId;
            
            // 2. 计算工序的计划总量和已录入总量
            // 注意：必须使用 order_worker.real_number（累计总量），避免 JOIN 重复计算
            let checkSql = `
                SELECT 
                    (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
                     FROM order_worker 
                     WHERE order_process_id = ?) AS planTotal,
                    (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
                     FROM order_worker 
                     WHERE order_process_id = ?) AS currentTotal,
                    o.state AS orderState
                FROM t_order o
                WHERE o.order_id = ?
            `;
            
            pool.query(checkSql, [orderProcessId, orderProcessId, orderId], (err, checkResult) => {
                if (err) {
                    console.error('检查订单状态失败:', err);
                    return res.send(utils.returnData({data: result}));
                }
                
                if (checkResult.length > 0) {
                    const planTotal = checkResult[0].planTotal || 0;
                    const currentTotal = checkResult[0].currentTotal || 0;
                    const orderState = checkResult[0].orderState;
                    
                    console.log('更新工人计划数后检查:', {
                        orderId: orderId,
                        orderProcessId: orderProcessId,
                        planTotal: planTotal,
                        currentTotal: currentTotal,
                        orderState: orderState
                    });
                    
                    // 3. 如果订单已完成，但当前录入量小于新的计划量，则回滚为进行中
                    if (orderState === 2 && currentTotal < planTotal) {
                        console.log('订单状态回滚为进行中:', orderId);
                        let updateOrderSql = `UPDATE t_order SET state = 1, update_time = now() WHERE order_id = ?`;
                        pool.query(updateOrderSql, [orderId], (err, updateResult) => {
                            if (err) {
                                console.error('回滚订单状态失败:', err);
                            } else {
                                console.log('订单状态已回滚为进行中');
                            }
                        });
                    }
                    // 4. 如果订单进行中，但当前录入量已达到新的计划量，检查所有工序是否完成
                    else if (orderState === 1 && currentTotal >= planTotal && planTotal > 0) {
                        console.log('当前工序已完成，检查订单所有工序是否完成');
                        
                        // 检查该订单的所有工序是否都完成
                        let checkAllProcessSql = `
                            SELECT 
                                op.order_process_id,
                                op.process_name,
                                (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
                                 FROM order_worker 
                                 WHERE order_process_id = op.order_process_id) AS planTotal,
                                (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
                                 FROM order_worker 
                                 WHERE order_process_id = op.order_process_id) AS realTotal
                            FROM order_process op
                            WHERE op.order_id = ?
                        `;
                        
                        pool.query(checkAllProcessSql, [orderId], (err, processResults) => {
                            if (err) {
                                console.error('检查所有工序状态失败:', err);
                                return;
                            }
                            
                            // 检查是否所有工序都完成
                            let allProcessCompleted = processResults.every(process => {
                                const pTotal = parseFloat(process.planTotal) || 0;
                                const rTotal = parseFloat(process.realTotal) || 0;
                                return rTotal >= pTotal && pTotal > 0;
                            });
                            
                            console.log('订单工序完成情况:', {
                                orderId: orderId,
                                totalProcess: processResults.length,
                                allCompleted: allProcessCompleted
                            });
                            
                            // 只有所有工序都完成时，才更新订单状态为已完成
                            if (allProcessCompleted && processResults.length > 0) {
                                console.log('所有工序已完成，更新订单状态为已完成:', orderId);
                        let updateOrderSql = `UPDATE t_order SET state = 2, update_time = now() WHERE order_id = ?`;
                        pool.query(updateOrderSql, [orderId], (err, updateResult) => {
                            if (err) {
                                console.error('更新订单状态失败:', err);
                            } else {
                                console.log('订单状态已更新为已完成');
                            }
                        });
                            } else {
                                console.log('还有其他工序未完成，订单保持进行中状态');
                            }
                        });
                    }
                }
                
                res.send(utils.returnData({data: result}));
            });
        });
    });
});
router.post("/getOrderBanner", async (req, res) => {
    let obj=req.body;
    let where='';
    
    // 添加日期范围筛选条件
    if(obj.startDate && obj.endDate){
        where = `WHERE DATE(create_time) >= '${obj.startDate}' AND DATE(create_time) <= '${obj.endDate}'`;
    }
    
    let sql=`SELECT
    SUM(1 = 1) AS total_count,
  SUM(state = 1) AS state1_count,
  SUM(state = 2) AS state2_count
  FROM t_order ${where}`;
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getOrder", async (req, res) => {
    let obj=req.body;
    let where='';
    if(obj.query){
        where+=`WHERE order_name LIKE "%${obj.query||''}%" `
    }
    let sql=`select id,order_id as orderId,order_name as orderName,customer_id as customerId,customer_name as customerName,state,remark,create_time,update_time,
  IF(state!=1,update_time,'') AS finishTime from t_order   ${where}   ORDER BY update_time DESC LIMIT ?,?`;
    
    // if(obj.createTime=='' || obj.createTime==null ||  obj.createTime.length==0){
    //     console.log(12133);
    //     sql= `SELECT a.id,CASE  WHEN a.create_time < '${CHANGE_TIME}'  THEN TRUE ELSE FALSE END AS is_his,a.massage_status as massageStatus,a.msgNum as msgNum, a.source,a.order_serial as orderSerial, a.order_id as orderId, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address FROM t_order a       WHERE (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")  and  a.sku_code like  "%lyf0%"  and a.state LIKE "%${obj.state||''}%"   `+ifStr+`  `+receiveTimeStr+`     ORDER BY  a.receive_time DESC,a.create_time desc,a.tracking_number LIMIT ?,?`;
    //     where=`WHERE (phone LIKE "%${obj.keyword||''}%" or order_id LIKE "%${obj.keyword||''}%")    and  sku_code like  "%lyf0%"   and state LIKE "%${obj.state||''}%"  `+ifwhereStr  +receiveTimeStrWhere;
    // }else{
    //     let startTime=obj.createTime[0];
    //     let endTime=obj.createTime[1];
    //     console.log(startTime);
    //     console.log(endTime);
    //     sql=`SELECT a.id,CASE WHEN a.create_time < '${CHANGE_TIME}'  THEN TRUE ELSE FALSE END AS is_his,a.massage_status as massageStatus,a.msgNum as msgNum,a.source, a.order_id as orderId,a.order_serial as orderSerial, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address  FROM t_order a     WHERE    a.create_time BETWEEN '${startTime}' and  '${endTime}'    and  a.sku_code like  "%lyf0%"   and  (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and a.state LIKE "%${obj.state||''}%"     `+ifStr+`    `+receiveTimeStr+`   ORDER BY a.receive_time DESC,a.create_time desc,a.tracking_number  LIMIT ?,?`;
    //     where=`WHERE  create_time BETWEEN '${startTime}' and  '${endTime}'  and  sku_code like  "%lyf0%"   and    (phone LIKE "%${obj.keyword||''}%" or order_id LIKE "%${obj.keyword||''}%")   and state LIKE "%${obj.state||''}%"   `+ifwhereStr  +receiveTimeStrWhere;
    // }
    let {total}=await utils.getSum({name:"t_order",where:`WHERE order_name LIKE "%${obj.query||''}%" `,res,req}) ;
    let {page,size}=utils.pageSize(obj.page,obj.size);
    pool.query(sql, [page,size],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result,total}));
    });
});
router.post("/getOrderById", async (req, res) => {
    let obj=req.body;
    let sql='select id,order_id as orderId,order_name as orderName,customer_id as customerId,customer_name as customerName,state,remark,create_time,update_time  from t_order where id=?';
    pool.query(sql, [obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/getOrderByIdMini", async (req, res) => {
    let obj=req.body;
    let sql=`
        SELECT 
        o.order_id AS orderId,
        o.order_name AS orderName,
        o.customer_name AS customerName,
        o.state,
        o.remark,
        ROUND(
            IF(SUM(ow.number) > 0, 
            SUM(ow.real_number) / SUM(ow.number) * 100, 
            0
            ), 2
        ) AS completionRate
        FROM t_order o
        LEFT JOIN order_process op ON o.order_id = op.order_id
        LEFT JOIN order_worker ow ON op.order_process_id = ow.order_process_id
        WHERE o.id = ?  
        GROUP BY o.order_id, o.order_name, o.customer_name, o.state, o.remark;
    `;
    pool.query(sql, [obj.id],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result[0]}));
    });
});
router.post("/getOrderByphone", async (req, res) => {
    let obj=req.body;
    let where='';
    
    // 添加工人ID筛选条件
    if(obj.id){
        where = `WHERE ow.worker_id = ${obj.id}`;
    }
    
    // 添加日期范围筛选条件（优先级：先搜索，后日期范围）
    if(obj.startDate && obj.endDate){
        if(where){
            where += ` AND DATE(o.create_time) >= '${obj.startDate}' AND DATE(o.create_time) <= '${obj.endDate}'`;
        } else {
            where = `WHERE DATE(o.create_time) >= '${obj.startDate}' AND DATE(o.create_time) <= '${obj.endDate}'`;
        }
    }
    
    // 添加状态筛选条件
    if(obj.state && obj.state !== '-1'){
        if(where){
            where += ` AND o.state = ${obj.state}`;
        } else {
            where = `WHERE o.state = ${obj.state}`;
        }
    }
    
    // 添加搜索条件（优先级最高）
    if(obj.query){
        let searchCondition = `(o.order_name LIKE '%${obj.query}%' OR o.customer_name LIKE '%${obj.query}%' OR op.process_name LIKE '%${obj.query}%')`;
        if(where){
            where += ` AND ${searchCondition}`;
        } else {
            where = `WHERE ${searchCondition}`;
        }
    }
    
    // 分页参数
    let page = obj.page || 1;
    let pageSize = obj.pageSize || 10;
    let offset = (page - 1) * pageSize;
    
    console.log('工人订单分页参数:', {
        page: page,
        pageSize: pageSize,
        offset: offset,
        month: obj.month,
        year: obj.year,
        state: obj.state,
        query: obj.query,
        workerId: obj.id
    });
    
    console.log('WHERE条件:', where);
    
    // 修改为以工人工序为维度进行分页和查询
    // 直接查询该工人的所有工序记录，按订单更新时间倒序排列
    let sql = `SELECT 
        o.order_id AS orderId,
        o.order_name AS orderName,
        o.customer_name AS customerName,
        o.state AS order_state,
        o.remark AS order_remark,
        o.update_time AS order_update_time,
        op.process_id AS processId,
        op.process_name AS processName,
        op.unit_id AS unitId,
        op.unit_name AS unitName,
        op.customerprice AS process_price,
        op.order_process_id AS orderProcessId,
        ow.id AS worker_data_id,
        ow.worker_id AS workerId,
        ow.woker_name AS workerName,
        ow.workerprice AS workerprice,
        ow.real_number AS real_number,
        ow.number,
        ROUND(IFNULL(ow.real_number, 0) * ow.workerprice, 2) AS realMoney,
        ow.phone
        FROM order_worker ow
        JOIN order_process op ON ow.order_process_id = op.order_process_id
        JOIN t_order o ON op.order_id = o.order_id
        ${where || 'WHERE ow.worker_id = ' + (obj.id || 0)}
        ORDER BY o.update_time DESC, op.order_process_id ASC
        LIMIT ${offset}, ${pageSize}`;
    
    console.log('工人工序查询SQL:', sql);
    
    pool.query(sql, [], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        console.log('查询结果数量:', result.length);
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getOrderBannerByphone", async (req, res) => {
    let obj=req.body;
    let where = `WHERE ow.worker_id = ${obj.id || 0}`;
    
    // 添加日期范围筛选条件
    if(obj.startDate && obj.endDate){
        where += ` AND DATE(o.create_time) >= '${obj.startDate}' AND DATE(o.create_time) <= '${obj.endDate}'`;
    }
    
    // 按工人工序维度统计：日期范围内的单量（工序数）和收益
    let sql=`SELECT
  COUNT(DISTINCT ow.order_process_id) AS month_order_count,
  COALESCE(SUM(ROUND(IFNULL(ow.real_number, 0) * ow.workerprice, 2)),0) AS month_realMoney
FROM order_worker ow
JOIN order_process op ON ow.order_process_id = op.order_process_id
JOIN t_order o ON op.order_id = o.order_id
${where}`;
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});


router.post("/getWorkloadByProcessId", async (req, res) => {
    let orderProcessId = req.body.orderProcessId;
    console.log('getWorkloadByProcessId 请求参数:', { orderProcessId });
    
    // 查询工序的计划总量和已录入总量
    // planTotal: 该工序所有工人的计划数量总和 (order_worker.number)
    // realTotal: 该工序所有工人的已录入数量总和 (order_worker.real_number)
    // 注意：必须使用 order_worker.real_number，因为这是累计总量
    // order_worker_numberlist.number 是每次录入的记录，可能有多条，不准确
    let sql = `SELECT 
        (SELECT COALESCE(SUM(IFNULL(number, 0)), 0) 
         FROM order_worker 
         WHERE order_process_id = ?) AS planTotal,
        (SELECT COALESCE(SUM(IFNULL(real_number, 0)), 0) 
         FROM order_worker 
         WHERE order_process_id = ?) AS realTotal`;
    
    pool.query(sql, [orderProcessId, orderProcessId], (err, result) => {
        if (err) {
            console.error('getWorkloadByProcessId 查询错误:', err);
            return res.send(utils.returnData({code: -1, err, req}));
        }
        
        const data = result[0] || {planTotal: 0, realTotal: 0};
        console.log('getWorkloadByProcessId 返回数据:', data);
        
        // 确保数值类型正确
        data.planTotal = parseFloat(data.planTotal) || 0;
        data.realTotal = parseFloat(data.realTotal) || 0;
        
        res.send(utils.returnData({data: data}));
    });
});


router.post("/getProcessByProcessIds", async (req, res) => {
    let obj=req.body;
    let sql=`SELECT 
  op.id,
  op.order_id AS orderId,
  op.order_process_id AS orderProcessId,
  op.process_id AS processId,
  op.process_name AS processName,
  op.unit_id AS unitId,
  op.unit_name AS unitName,
  op.customerprice,
  op.update_time,
  op.create_time,
  ROUND(
    IF(SUM(ow.number) > 0, 
       SUM(ow.real_number) / SUM(ow.number) * 100, 
       0
    ), 2
  ) AS completionRate
FROM order_process op
LEFT JOIN order_worker ow ON op.order_process_id = ow.order_process_id
WHERE op.order_id = ?
GROUP BY 
  op.id, 
  op.order_id, 
  op.order_process_id, 
  op.process_id, 
  op.process_name, 
  op.unit_id, 
  op.unit_name, 
  op.customerprice, 
  op.update_time, 
  op.create_time
ORDER BY op.order_process_id`;
    pool.query(sql, [obj.orderId],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});









// 迁移工人数量记录：当工序ID变化时，将旧的工人数量记录迁移到新的工序ID
router.post("/migrateWorkerNumberRecords", async (req, res) => {
    let obj = req.body;
    let oldOrderProcessId = obj.oldOrderProcessId;
    let newOrderProcessId = obj.newOrderProcessId;
    
    if (!oldOrderProcessId || !newOrderProcessId) {
        return res.send(utils.returnData({code: -1, msg: "参数错误", req}));
    }
    
    // 更新工人数量记录中的processId（实际存储的是orderProcessId）
    let updateSql = `UPDATE order_worker_numberlist SET processId = ? WHERE processId = ?`;
    pool.query(updateSql, [newOrderProcessId, oldOrderProcessId], (err, result) => {
        if (err) {
            console.error('迁移工人数量记录失败:', err);
            return res.send(utils.returnData({code: -1, err, req}));
        }
        console.log(`成功迁移 ${result.affectedRows} 条工人数量记录: ${oldOrderProcessId} -> ${newOrderProcessId}`);
        res.send(utils.returnData({
            data: {
                affectedRows: result.affectedRows,
                message: `成功迁移 ${result.affectedRows} 条工人数量记录`
            }
        }));
    });
});

router.post("/getWorkerByWorkerIds", async (req, res) => {
    let obj=req.body;
    let sql=`select id,order_process_id as orderProcessId,worker_id as workerId,woker_name as workerName,phone,workerprice,number,real_number as realNumber,update_time,create_time  from order_worker where order_process_id =?`;
    pool.query(sql, [obj.orderProcessId],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});
router.post("/getOrderByMini", async (req, res) => {
    let obj=req.body;
    let where='';
    
    // 添加日期范围筛选条件（优先级：先搜索，后日期范围）
    if(obj.startDate && obj.endDate){
        where = `WHERE DATE(o.create_time) >= '${obj.startDate}' AND DATE(o.create_time) <= '${obj.endDate}'`;
    }
    
    // 添加状态筛选条件
    if(obj.state && obj.state !== '-1'){
        if(where){
            where += ` AND o.state = ${obj.state}`;
        } else {
            where = `WHERE o.state = ${obj.state}`;
        }
    }
    
    // 添加搜索条件（优先级最高）
    if(obj.query){
        let searchCondition = `(o.order_id LIKE '%${obj.query}%' OR o.order_name LIKE '%${obj.query}%' OR o.customer_name LIKE '%${obj.query}%')`;
        if(where){
            where += ` AND ${searchCondition}`;
        } else {
            where = `WHERE ${searchCondition}`;
        }
    }
    
    // 分页参数
    let page = obj.page || 1;
    let pageSize = obj.pageSize || 10;
    let offset = (page - 1) * pageSize;
    
    console.log('分页参数:', {
        page: page,
        pageSize: pageSize,
        offset: offset,
        startDate: obj.startDate,
        endDate: obj.endDate,
        state: obj.state,
        query: obj.query
    });
    
    console.log('WHERE条件:', where);
    
    // 先获取需要分页的订单ID
    let orderIdsSql = `SELECT DISTINCT o.order_id 
    FROM t_order o
    ${where || 'WHERE 1=1'}
    ORDER BY o.update_time DESC
    LIMIT ${offset}, ${pageSize}`;
    
    console.log('订单ID查询SQL:', orderIdsSql);
    
    // 先查询订单ID
    pool.query(orderIdsSql, [], (err, orderIds) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        
        if (orderIds.length === 0) {
            return res.send(utils.returnData({data: []}));
        }
        
        // 构建订单ID的IN条件
        let orderIdList = orderIds.map(item => `'${item.order_id}'`).join(',');
        
        // 根据订单ID查询详细信息
        let sql = `SELECT 
        o.id,
        o.order_id AS orderId,
        o.order_name AS orderName,
        o.customer_name AS customerName,
        o.state,
        o.remark,
        o.update_time AS updateTime,
        GROUP_CONCAT(DISTINCT op.process_name ORDER BY op.process_id SEPARATOR ', ') AS processNames,
        GROUP_CONCAT(DISTINCT ow.woker_name ORDER BY ow.worker_id SEPARATOR ', ') AS workerNames,
        SUM(ow.number) AS totalNumber,
        SUM(ow.real_number) AS totalRealNumber,
        ROUND(
          IF(SUM(ow.number) > 0, 
             SUM(ow.real_number) / SUM(ow.number) * 100, 
             0
          ), 2
        ) AS completionRate
        FROM t_order o
        JOIN order_process op ON o.order_id = op.order_id
        JOIN order_worker ow ON op.order_process_id = ow.order_process_id
        WHERE o.order_id IN (${orderIdList})
        GROUP BY o.order_id  
        ORDER BY o.update_time DESC`;
        
        console.log('详细查询SQL:', sql);
        
        pool.query(sql, [], (err2, result) => {
            if (err2) return res.send(utils.returnData({code: -1,err:err2,req}));
            console.log('查询结果数量:', result.length);
            res.send(utils.returnData({data: result}));
        });
    });
});
router.post("/getAllOrder", async (req, res) => {
    req.setTimeout(20*1000);
    let obj=req.body;
    console.log(12345,obj);
    let sql='';
    let trackIngType=obj.trackIngType;
    let receiveTime=obj.receiveTime;
    let receiveTimeStr="";
    let ifStr="";
    if(trackIngType==='0'){
        ifStr=" and  a.tracking_number = ''  ";
    }
    if(trackIngType==='1'){
        console.log(9999);
        ifStr=" and  a.tracking_number != ''  ";
    }
    if(receiveTime!=''&& receiveTime!=null && receiveTime.length!=0){
        console.log(8888);
        let reSTime=receiveTime[0];
        let reeTime=receiveTime[1];
        console.log(reSTime);
        console.log(reeTime);
        receiveTimeStr=`and a.receive_time BETWEEN '${reSTime}' and  '${reeTime}'`;
    }
    if(obj.createTime=='' || obj.createTime==null ||  obj.createTime.length==0){
        sql= `SELECT a.id, a.source,a.order_serial as orderSerial, a.order_id as orderId, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address FROM t_order a  WHERE (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and  a.sku_code like  "%lyf0%"  and a.state LIKE "%${obj.state||''}%"   `+ifStr+`  `+receiveTimeStr+`    ORDER BY a.receive_time DESC,a.create_time desc,a.tracking_number  `;
    }else{
        let startTime=obj.createTime[0];
        let endTime=obj.createTime[1];
        console.log(startTime);
        console.log(endTime);
        sql=`SELECT a.id, a.source, a.order_id as orderId,a.order_serial as orderSerial, a.phone, a.receive_time AS receiveTime, a.ext_code as extCode, a.quantity AS quantity, a.rights_id as rightsid, a.price, a.sku_id as skuId, a.sku_code as skuCode, a.sku_name AS skuName,  a.user_consignee_id, a.express_company, a.tracking_number AS trackingNumber, a.state AS state, a.sms_num, a.create_time AS createTime, a.update_time, a.created_at,a.user_consignee_name AS consigneeName,a.user_consignee_phone AS consigneePhone,a.user_consignee_address  AS  address  FROM t_order a    WHERE    a.create_time BETWEEN '${startTime}' and  '${endTime}'  and  a.sku_code like  "%lyf0%"  and  (a.phone LIKE "%${obj.keyword||''}%"  or  a.order_id LIKE "%${obj.keyword||''}%")   and a.state LIKE "%${obj.state||''}%"     `+ifStr+`   `+receiveTimeStr+`    ORDER BY a.receive_time DESC,a.create_time desc,a.tracking_number `;
    }
    console.log(123456,sql);
    pool.query(sql, [],(err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});




//删除订单
router.post("/delOrder", async (req, res) => {
    let sql = "DELETE FROM t_order WHERE id=?", obj = req.body;
    pool.query(sql, [obj.id], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});

router.post("/delToOrder", async (req, res) => {
    let sql = "DELETE FROM t_order WHERE id in (?)", obj = req.body;
    pool.query(sql, [obj.ids], (err, result) => {
        if (err) return res.send(utils.returnData({code: -1,err,req}));
        res.send(utils.returnData({data: result}));
    });
});




  //测试
  router.get('/test',(req,res) => {
    const query = req.query
    res.send({
        msg:'测试专用',
        data:{}
    })
})


module.exports = router;
